# -*- coding: utf-8 -*-
import re
import sqlite3
import requests
import time
import traceback
from datetime import datetime, timedelta


def cleanhtml(html):
    pattern = '\s+|(&nbsp;)+'
    # pattern='\s+'
    html = re.sub(pattern, ' ', html)
    # 去掉html标记间的空格
    # pattern='> <'
    # html=re.sub(pattern,'><',html)
    return html.replace('> <', '><')


def getfetch():
    auth_url = 'http://127.0.0.1:50003/auth_npedi'
    url = 'http://www.npedi.com/edi/voyageInfoAction.do'
    auth = requests.get(auth_url, params={'site': 'npedi', 'action': 'remote'}, headers={
                        'Accept': 'application/json'}).json()
    print auth
    cookies = dict(JSESSIONID=auth['cookie'])
    rows = []
    cpcodes = ('BLCT', 'BLCT2', 'BLCT3', 'BLCTYD', 'BLCTZS', 'BLCTMS', 'YZCT', 'B2SCT', 'ZHCT', 'DXCTE', 'ZIT')

    def op():
        i = 0
        while True:
            i += 1
            if i > 30:
                return
            params = {'ename': '', 'voyage': '', 'pageIndex': i, 'cpcode': cpcode}
            try:
                res = requests.get(url, params=params, cookies=cookies)
            except Exception, e:
                print e
                print traceback.format_exc()
                continue
            tds = cleanval(re.findall(
                '<td height="24" nowrap="nowrap" class="dblue12">(.*?)</td>', cleanhtml(res.text)))
            if tds:
                print i, len(tds), tds[1]
                for j in range(len(tds) / 16):
                    if tds[j * 16 + 11]:
                        yjdgsj = datetime.strptime(
                            tds[j * 16 + 11], '%Y-%m-%d %H:%M')
                        if yjdgsj < datetime.now() - timedelta(days=1):
                            return
                    row = []
                    for k in tds[j * 16 + 1:j * 16 + 16]:
                        row.append(k)
                    rows.append(row)
    for cpcode in cpcodes:
        op()
    return rows


def cleanval(v):
    if type(v) == list or type(v) == tuple:
        return [None if x.strip() == '' else x.strip() for x in v]
    else:
        return None if v.strip() == '' else v.strip()


# ##########################################
# 主程序开始
# ##########################################
# head=('xh',   'tkmt',   'ywcm',   'hgywcm',   'zwcm',   'mthc',   'hghc',   'hx',   'undm',   'jxkssj',   'jxjzsh',   'yjdgsj',   'sjdgsj',   'yjlgsj',   'sjlgsj',   'jdsj')
head = ('tkmt',   'ywcm',   'hgywcm',   'zwcm',   'mthc',   'hghc',   'hx',   'undm', 'jxkssj',   'jxjzsj',   'yjdgsj',   'sjdgsj',   'yjlgsj',   'sjlgsj',   'jdsj')

s = time.time()
rs = getfetch()
print 'fetch use second:', time.time() - s

s = time.time()
conn = sqlite3.connect('npedi.db3')
sql = 'delete from voyageinfo'
conn.execute(sql)

for r in rs:
    cols = ','.join(head)
    values = ','.join(['?'] * len(head))
    sql = 'insert into voyageinfo (%s) values(%s)' % (cols, values)
    conn.execute(sql, r)
conn.commit()
conn.close()
print 'write db use second:', time.time() - s
